<?php 
include_once('connection/connection.php');
include('header.php');
include('menu.php');
include('config.php');
$district=$_SESSION['district'];
function getName($colName,$id)
{
	//$arrColName=explode('_',$colName);
	//$colNameText=$arrColName[0]."_name";
	$sqlgetName="select distinct ".$colName."_name from tbl_lookup_location where ".$colName."_id=".$id;
	$querygetName=mysql_query($sqlgetName);
	$rowgetName=mysql_fetch_row($querygetName);
	return $rowgetName[0];
}
?>
<html>
<head>
<meta http-equiv="Content-Language" content="en-us">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Critical Village Report</title>
<link href="css/critical.css" type="text/css" rel="stylesheet" />
<style type="text/css">

.style10 {font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 9px; color:#000000}
</style>
</head>
<body>
<?php
/*
$Supply_data=0;
$distId= $district;//418;//$_REQUEST['distId'];
$blockId=$block;//4308;//$_REQUEST['blockId'];
//$villageId=366208;//$_REQUEST['villageId'];

//Select Data from lookup location table village wise
$sqlReport = "SELECT distinct block_id,village_id,block_name,village_name,census_village_code FROM tbl_lookup_location WHERE district_id='$distId' and block_id='$blockId' order by village_name";
$queryReport = mysql_query($sqlReport);

//query for find Population factor of corresponding district
$sqlPopFactor="select * from tbl_lookup_pop_factor where district_id='$distId'";
$queryPopFactor= mysql_query($sqlPopFactor);
$rowPopFactor=mysql_fetch_array($queryPopFactor);
$population_factor=$rowPopFactor['population_factor'];*/

$qryCritical="select * from tbl_dtls_critical where district_id='$district'";
$recCritical=mysql_query($qryCritical);

//find lpcd for district
if(($distId == 389) || ($distId ==390) || ($distId == 391) || ($distId ==392) || ($distId == 401) || ($distId == 402) || ($distId == 403) || ($distId == 404) || ($distId == 405) || ($distId == 406) || ($distId == 416))
{
	$lpcd=60;
}
else
{
	$lpcd=30;
}

?>

<table border="0" width="2420" cellspacing="0" cellpadding="0">
	<tr>
		<td width="2420" height="26" bgcolor="#E9E9E9">
		<p align="center"><span style="font-size: 9pt; font-weight: 700">
	  <font face="Verdana" color="#224568">Critical Village Report</font></span></td>
	</tr>
	<tr>
	  <td bgcolor="#E9E9E9" height="26"><div align="center"><font face="Verdana" style="font-size: 9pt; font-weight: 700" color="#224568">&nbsp;</font></div></td>
  </tr>
	
	<tr>
	  <td><table border="1" width="2670" cellspacing="0" cellpadding="0" height="265" bordercolor="#FFFFFF" style="border: 1px solid #BBD1E8" bgcolor="#F9F9F9">
        <tr bgcolor="#C5D8EB">
          <td width="17" rowspan="3" align="center"><font face="Verdana" style="font-size: 9.5pt" color="#000000">SN</font></td>
          <td width="73" rowspan="3" align="center"><font face="Verdana" style="font-size: 9.5pt" color="#000000">Name of Block</font></td>
          <td width="137" rowspan="3" align="center"><font face="Verdana" style="font-size: 9.5pt" color="#000000">Name of Village</font></td>
          <td width="88" rowspan="3" align="center"><font face="Verdana" style="font-size: 9.5pt" color="#000000">Census Village Code</font></td>
          <td colspan="4" rowspan="3" align="center"><font face="Verdana" style="font-size: 9.5pt" color="#000000">Type of Scheme (HP/P&T/Piped/RWSS/Other)</font></td>
          <td width="74" rowspan="3" align="center"><font face="Verdana" style="font-size: 9.5pt" color="#000000">Operation Status (F/NF)</font></td>
          <td colspan="2" rowspan="3" align="center"><font face="Verdana" style="font-size: 9.5pt" color="#000000">Category of the Village (Gen/SC/ST)</font></td>
          <td colspan="2" rowspan="3" align="center"><font face="Verdana" style="font-size: 9.5pt" color="#000000">Population of the Village (2010)</font></td>
          <td colspan="6" align="center"><font face="Verdana" style="font-size: 9.5pt" color="#000000">Water Requirement of the Village (KLD)</font></td>
          <td colspan="6" align="center"><font face="Verdana" style="font-size: 9.5pt" color="#000000">Functional Hand Pumps</font></td>
          <td colspan="3" rowspan="3" align="center"><font face="Verdana" style="font-size: 9.5pt" color="#000000">Non Functional HP (Nos.)</font></td>
          <td colspan="6" align="center"><font face="Verdana" color="#000000"> <span style="font-size: 8.5pt">Functional TWs/OWs</span></font></td>
          <td colspan="3" rowspan="3" align="center"><font face="Verdana" style="font-size: 9.5pt" color="#000000">Non Functional TWs/OWs (Nos.)</font></td>
		  <td width="103" rowspan="3" align="center"><font face="Verdana" style="font-size: 9.5pt" color="#000000">Total local production (KLD)</font></td>
          <td colspan="3" rowspan="2" align="center"><font face="Verdana" color="#000000"> <span style="font-size: 8.5pt">Total Quantity of Water Supply to Village (KLD)</span></font></td>
          <td colspan="3" rowspan="2" align="center"><font face="Verdana" color="#000000"> <span style="font-size: 8.5pt">Excess/ Shortfall (KLD)</span></font></td>
          <td width="48" rowspan="3" align="center"><font face="Verdana" color="#000000"> <span style="font-size: 8.5pt">Reason for Shortfall</span></font></td>
          <td colspan="3" rowspan="3" align="center"><font face="Verdana" color="#000000"> <span style="font-size: 8.5pt">Suggested Measures</span></font></td>
          <td width="60" rowspan="3" align="center"><font face="Verdana" color="#000000"> <span style="font-size: 8.5pt">Likely cost of the measures (Rs. in Lacs)</span></font></td>
          <td width="185" rowspan="3" align="center"><font face="Verdana" color="#000000"> <span style="font-size: 8.5pt">Remarks</span></font></td>
        </tr>
        <tr bgcolor="#C5D8EB">
          <td colspan="2" rowspan="2" align="center"><font face="Verdana" style="font-size: 9.5pt" color="#000000">For Drinking & Cooking</font></td>
          <td colspan="2" rowspan="2" align="center"><font face="Verdana" style="font-size: 9.5pt" color="#000000">For Other Needs</font></td>
          <td colspan="2" rowspan="2" align="center"><font face="Verdana" style="font-size: 9.5pt" color="#000000">Total</font></td>
          <td colspan="2" align="center"><font face="Verdana" style="font-size: 9.5pt" color="#000000">Portable</font></td>
          <td colspan="2" align="center"><font face="Verdana" style="font-size: 9.5pt" color="#000000">Non Portable</font></td>
          <td colspan="2" align="center"><font face="Verdana" style="font-size: 9.5pt" color="#000000">Total</font></td>
          <td colspan="2" align="center"><font face="Verdana" style="font-size: 9.5pt" color="#000000">Portable</font></td>
          <td colspan="2" align="center"><font face="Verdana" style="font-size: 9.5pt" color="#000000">Non Portable</font></td>
          <td colspan="2" align="center"><font face="Verdana" style="font-size: 9.5pt" color="#000000">Total</font></td>
        </tr>
        <tr bgcolor="#C5D8EB">
          <td width="28" align="center"><font face="Verdana" style="font-size: 9.5pt" color="#000000">Nos.</font></td>
          <td width="71" align="center"><font face="Verdana" style="font-size: 9.5pt" color="#000000">Production (KLD)</font></td>
          <td width="28" align="center"><font face="Verdana" style="font-size: 9.5pt" color="#000000">Nos.</font></td>
          <td width="71" align="center"><font face="Verdana" style="font-size: 9.5pt" color="#000000">Production (KLD)</font></td>
          <td width="28" align="center"><font face="Verdana" style="font-size: 9.5pt" color="#000000">Nos.</font></td>
          <td width="71" align="center"><font face="Verdana" style="font-size: 9.5pt" color="#000000">Production (KLD)</font></td>
          <td width="64" align="center"><font face="Verdana" style="font-size: 9.5pt" color="#000000">Nos.</font></td>
          <td width="71" align="center"><font face="Verdana" style="font-size: 9.5pt" color="#000000">Production (KLD)</font></td>
          <td width="64" align="center"><font face="Verdana" style="font-size: 9.5pt" color="#000000">Nos.</font></td>
          <td width="71" align="center"><font face="Verdana" style="font-size: 9.5pt" color="#000000">Production (KLD)</font></td>
          <td width="64" align="center"><font face="Verdana" style="font-size: 9.5pt" color="#000000">Nos.</font></td>
          <td width="71" align="center"><font face="Verdana" style="font-size: 9.5pt" color="#000000">Production (KLD)</font></td>
          <td width="53" align="center"><font face="Verdana" style="font-size: 9.5pt" color="#000000">Portable</font></td>
          <td width="53" align="center"><font face="Verdana" style="font-size: 9.5pt" color="#000000">Non Portable</font></td>
          <td width="31" align="center"><font face="Verdana" style="font-size: 9.5pt" color="#000000">Total</font></td>
          <td width="55" align="center"><font face="Verdana" style="font-size: 9.5pt" color="#000000">For Drinking & Cooking</font></td>
          <td width="40" align="center"><font face="Verdana" style="font-size: 9.5pt" color="#000000">For Other Needs</font></td>
          <td width="31" align="center"><font face="Verdana" style="font-size: 9.5pt" color="#000000">Total</font></td>
        </tr>
        <tr>
          <td height="13" align="center"><span class="style10">1</span></td>
          <td align="center"><span class="style10">2</span></td>
          <td align="center"><span class="style10">3</span></td>
          <td align="center"><span class="style10">4</span></td>
          <td colspan="4" align="center"><span class="style10">5</span></td>
          <td align="center"><span class="style10">6</span></td>
          <td colspan="2" align="center"><span class="style10">7</span></td>
          <td colspan="2" align="center"><span class="style10">8</span></td>
          <td colspan="2" align="center"><span class="style10">9</span></td>
          <td colspan="2" align="center"><span class="style10">10</span></td>
          <td colspan="2" align="center"><span class="style10">11</span></td>
          <td align="center"><span class="style10">12</span></td>
          <td align="center"><span class="style10">13</span></td>
          <td align="center"><span class="style10">14</span></td>
          <td align="center"><span class="style10">15</span></td>
          <td align="center"><span class="style10">16</span></td>
          <td align="center"><span class="style10">17</span></td>
          <td colspan="3" align="center"><span class="style10">18</span></td>
          <td align="center"><span class="style10">19</span></td>
          <td align="center"><span class="style10">20</span></td>
          <td align="center"><span class="style10">21</span></td>
          <td align="center"><span class="style10">22</span></td>
          <td align="center"><span class="style10">23</span></td>
          <td align="center"><span class="style10">24</span></td>
          <td colspan="3" align="center"><span class="style10">25</span></td>
          <td align="center"><span class="style10">26</span></td>
          <td align="center"><span class="style10">27</span></td>
          <td align="center"><span class="style10">28</span></td>
          <td align="center"><span class="style10">29</span></td>
          <td align="center"><span class="style10">30</span></td>
		  <td align="center"><span class="style10">31</span></td>
		  <td align="center"><span class="style10">32</span></td>
          <td colspan="3" align="center"><span class="style10">33</span></td>
          <td width="60" align="center"><span class="style10">34</span></td>
          <td align="center"><span class="style10">35</span></td>
		  <td align="center"><span class="style10">36</span></td>
        </tr>
		<div id="critical">
		<?php
		$sn=1;
		while($rowCritical=mysql_fetch_array($recCritical)){
		?>
		
        <tr>
          <td height="13" align="center"><?php echo $sn++;?></td>
          <td align="center"><?php $id="$rowCritical[block_id]"; echo getName('block',$id);?></td>
          <td align="center"><?php $id="$rowCritical[village_id]"; echo getName('village',$id);?></td>
          <td align="center"><?php echo $rowCritical['cvc'];?></td>
          <td colspan="4" align="center"><?php echo $rowCritical['scheme_type'];?></td>
          <td align="center"><?php echo $rowCritical['scheme_status'];?></td>
          <td colspan="2" align="center">
		  <?php 
		  ///////////////////////////////////
		  $total_population=$rowCritical['population_current'];
		  
		 $qryCritical1="select sum(population_sc),sum(population_st),sum(population_other) from tbl_dtls_critical_habit a,tbl_dtls_critical b where b.id=a.loc_id and b.village_id='$rowCritical[village_id]'";
          $recCritical1=mysql_query($qryCritical1);
          $rowPopulation=mysql_fetch_row($recCritical1);
			    $sc_population=$rowPopulation[0];
			    $st_population=$rowPopulation[1];
			    $sc_st_population=$sc_population + $st_population;
			    $gen_population=$rowPopulation[2];
				
				if($total_population!=0)
			    {
				  if(($sc_st_population/$total_population)>0.5)
				   {
				     if(($sc_population/$sc_st_population)>0.5)
				      {
					$column13="SC";
				      }else{
				    $column13="ST";
				           }
				 }
				 else
				 {
					$column13="GEN";
				 }
			  }
			  else
			  {
				if(sc_st_population>0.5)
				{
					$column13="SC/ST";
				}
				else
				{
					$column13="GEN";
				}
			 } //end of column13
		  /////////////////////////////////
		  
		  echo $column13;
		  
		  ?></td>
          <td colspan="2" align="center"><?php echo $pop=$rowCritical['population_current'];?></td>
          <td colspan="2" align="center"><?php echo $dc=($pop*10)/1000;?></td>
          <td colspan="2" align="center"><?php echo $ot=($pop*$lpcd)/1000;?></td>
          <td colspan="2" align="center"><?php echo $dc+$ot;?></td>
          <td align="center"><?php echo $rowCritical['hp_potable'];?></td>
          <td align="center"><?php echo $hp_port=$rowCritical['hp_potable']*10;?></td>
          <td align="center"><?php echo $np=$rowCritical['hp_total']-$rowCritical['hp_potable'];?></td>
          <td align="center"><?php echo $hp_np=$np*10;?></td>
          <td align="center"><?php echo $rowCritical['hp_total'];?></td>
          <td align="center"><?php echo $hp_tot=$rowCritical['hp_total']*10;?></td>
          <td colspan="3" align="center">
		  <?php 
		         
			  $qrySourceTW="SELECT count(*) FROM tbl_dtls_q3_water_source_detail src,tbl_dtls_q3_loc loc where loc.id=src.loc_id and loc.village_id='$rowCritical[village_id]' and functional_status='NF' and source_type like 'TW%'";
              $recSourceTW=mysql_query($qrySourceTW);
              $rowSourceTW=mysql_fetch_row($recSourceTW);    
		  
		  
		      $qryHP="SELECT count(*) FROM tbl_dtls_q3_water_source_detail src,tbl_dtls_q3_loc loc where loc.id=src.loc_id and loc.village_id='$rowCritical[village_id]' and functional_status='NF' and source_type like 'HpGovt%'";
              $recHP=mysql_query($qryHP);
              $rowHP=mysql_fetch_row($recHP);
		  
		  echo $rowHP[0];
		  ?></td>
          <td align="center"><?php 
		  if($rowCritical['tw_potable']!='')
		  {
		  echo $tw_po=$rowCritical['tw_potable'];
		  }else{
		  echo '0';
		  }
		  ?></td>
          <td align="center"><?php echo $tw_port=$tw_po*$rowCritical['tw_yield']*8;?></td>
          <td align="center"><?php echo $tw_np=$rowCritical['tw_total'] - $rowCritical['tw_potable'];?></td>
          <td align="center"><?php echo $tw_np=$tw_np*$rowCritical['tw_yield']*8;?></td>
          <td align="center"><?php echo $tw_tot=$rowCritical['tw_total'];?></td>
          <td align="center"><?php echo $tw_tot=$tw_tot*$rowCritical['tw_yield']*8;?></td>
          <td colspan="3" align="center"><?php echo $rowSourceTW[0];?></td>
		  <td align="center"><?php echo $hp_tot +  $tw_tot;?></td>
          <td align="center"><?php echo $hp_port + $tw_port;?></td>
          <td align="center"><?php echo $hp_np + $tw_np;?></td>
          <td align="center"><?php echo $hp_port + $tw_port + $hp_np + $tw_np;?></td>
          <td align="center"><?php echo ($hp_port + $tw_port) - $dc;?></td>
          <td align="center"><?php echo ($hp_np + $tw_np) - $ot;?></td>
          <td align="center"><?php echo ($hp_port + $tw_port + $hp_np + $tw_np) - ($dc+$ot);?></td>
          <td align="center">&nbsp;</td>
          <td colspan="3" align="center">&nbsp;</td>
          <td align="center">&nbsp;</td>
          <td align="center">&nbsp;<?php //echo $rowCritical['remark'];?></td>
        </tr>
        <?php }?>
        
        </div>
      </table></td>
	</tr>
</table>

<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
</body>
</html>